﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.IO;
using Excel;
using System.Linq;
namespace Exact_Extension
{
    public partial class ImportCashBank : DevExpress.XtraEditors.XtraForm
    {
        ExactDataContext DS;
        Common common;
        public ImportCashBank(ExactDataContext _DS)
        {
            InitializeComponent();
            DS = _DS;
            common = new Common(DS);
         

        }
        private string GetDebtorCode(string studentID)
        {
            var query = from ST in DS.ISHCMC_V_StudentListWithDebtors
                        where ST.StudentID == studentID
                        select ST;
            if (query.Count() > 0)
            {
                return query.First().DebtorCode.Trim();
            }
            else
            {
                return "";
            }
        }

        private string GetSchool(string studentID)
        {
            var query = from ST in DS.ISHCMC_V_StudentListWithDebtors
                        where ST.StudentID == studentID
                        select ST;
            if (query.Count() > 0)
            {
                return query.First().School;
            }
            else
            {
                return "";
            }
        }
        private void barButtonItem1_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DialogResult resultDialog  = this.openFileDialog1.ShowDialog();
            if (resultDialog == DialogResult.OK)
            {
                this.txtFileImport.EditValue = this.openFileDialog1.FileName;
                FileInfo fileinfo = new FileInfo(this.txtFileImport.EditValue.ToString());
                FileStream stream = File.Open(this.txtFileImport.EditValue.ToString(), FileMode.Open, FileAccess.Read);
                IExcelDataReader excelReader = null;
                if (fileinfo.Extension == ".xls")
                {
                    //1. Reading from a binary Excel file ('97-2003 format; *.xls)
                    excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                if (fileinfo.Extension == ".xlsx")
                {
                    //...
                    //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
                    excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }

                //4. DataSet - Create column names from first row
                excelReader.IsFirstRowAsColumnNames = true;
                DataSet result = excelReader.AsDataSet();

                //5. Data Reader methods
                List<FinEntry> ListFinEntry = new List<FinEntry>();
                int i = 0;
                while (excelReader.Read())
                {
                    if (i > 0)
                    {
                        FinEntry item = new FinEntry();
                        item.StudentID = excelReader.GetString(0);
                        item.StudentName = excelReader.GetString(1);
                        item.Ref = excelReader.GetString(2);
                        item.DebtorCode = this.GetDebtorCode(item.StudentID);
                        item.InvoiceNo = excelReader.GetString(3);
                        item.School = this.GetSchool(item.StudentID);
                        item.ReceiptDate = excelReader.GetDateTime(4);
                        item.Amount = Convert.ToDouble(excelReader.GetString(5));

                        item.Currency = excelReader.GetString(6);
                        item.GLAccount = excelReader.GetString(7);
                        item.GLAccountOffset = excelReader.GetString(8);
                        item.JournalNumber = common.GetJournalNumber(item.GLAccount);
                        ListFinEntry.Add(item);
                    }
                    i++;
                }

                //6. Free resources (IExcelDataReader is IDisposable)
                excelReader.Close();
                this.finEntryBindingSource.DataSource = ListFinEntry;
            }
        }

        private void btnImport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            try
            {
                XMLExport xml = new XMLExport(DS);
                List<FinEntry> ListData = new List<FinEntry>();
                foreach (int i in this.gridView_Import.GetSelectedRows())
                {
                    ListData.Add((FinEntry)this.gridView_Import.GetRow(i));
                }
                string filename = xml.ExportFinancialEntryCashBank(ListData);
                xml.StartProcessImportFIN(filename);
                MessageBox.Show("Import Financial Entries Bank/Cash sucessfully");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Import occured error , please contact administrator");
            }
        }
    }
}